import pymysql
from typing import Optional, Dict, Any
from config import MYSQL_CONFIG
import logging

logger = logging.getLogger(__name__)

def create_mysql_connection(config: Optional[Dict[str, Any]] = None) -> pymysql.Connection:
    """
    创建MySQL数据库连接
    
    Args:
        config: MySQL配置字典，如果为None则使用config.py中的MYSQL_CONFIG
        
    Returns:
        pymysql.Connection: MySQL连接对象
        
    Raises:
        pymysql.Error: 当连接失败时抛出异常
    """
    if config is None:
        config = MYSQL_CONFIG
    
    try:
        connection = pymysql.connect(
            host=config['host'],
            port=config['port'],
            user=config['username'],
            password=config['password'],
            database=config['database'],
            charset='utf8mb4',
            autocommit=True,
            cursorclass=pymysql.cursors.DictCursor
        )
        
        logger.info(f"成功连接到MySQL数据库: {config['host']}:{config['port']}/{config['database']}")
        return connection
        
    except pymysql.Error as e:
        logger.error(f"连接MySQL数据库失败: {e}")
        raise

def get_mysql_connection() -> pymysql.Connection:
    """
    获取MySQL数据库连接的便捷函数
    
    Returns:
        pymysql.Connection: MySQL连接对象
    """
    return create_mysql_connection()

def test_mysql_connection() -> bool:
    """
    测试MySQL数据库连接是否正常
    
    Returns:
        bool: 连接成功返回True，失败返回False
    """
    try:
        connection = create_mysql_connection()
        with connection.cursor() as cursor:
            cursor.execute("SELECT 1")
            result = cursor.fetchone()
            logger.info("MySQL连接测试成功")
            return True
    except Exception as e:
        logger.error(f"MySQL连接测试失败: {e}")
        return False
    finally:
        if 'connection' in locals():
            connection.close()

def insert_example_with_timestamp(table_name: str, data: Dict[str, Any]) -> bool:
    """
    插入数据的示例函数，支持timestamp类型数据
    
    Args:
        table_name: 表名
        data: 要插入的数据字典，支持timestamp类型
        
    Returns:
        bool: 插入成功返回True，失败返回False
        
    Example:
        data = {
            'name': '张三',
            'email': 'zhangsan@example.com',
            'created_at': datetime.now(),
            'updated_at': datetime.now(),
            'status': 1
        }
        insert_example_with_timestamp('users', data)
    """
    connection = None
    try:
        connection = create_mysql_connection()
        with connection.cursor() as cursor:
            # 构建SQL语句
            columns = list(data.keys())
            placeholders = ', '.join(['%s'] * len(columns))
            columns_str = ', '.join(columns)
            
            sql = f"INSERT INTO {table_name} ({columns_str}) VALUES ({placeholders})"
            
            # 准备数据，处理timestamp类型
            values = []
            for key, value in data.items():
                if hasattr(value, 'strftime'):  # 检查是否为datetime对象
                    # 将datetime对象转换为MySQL格式的字符串
                    values.append(value.strftime('%Y-%m-%d %H:%M:%S'))
                else:
                    values.append(value)
            
            # 执行插入
            cursor.execute(sql, values)
            connection.commit()
            
            logger.info(f"成功插入数据到表 {table_name}，影响行数: {cursor.rowcount}")
            return True
            
    except Exception as e:
        logger.error(f"插入数据到表 {table_name} 失败: {e}")
        if connection:
            connection.rollback()
        return False
    finally:
        if connection:
            connection.close()

def batch_insert_with_timestamp(table_name: str, data_list: list) -> bool:
    """
    批量插入数据的示例函数，支持timestamp类型数据
    
    Args:
        table_name: 表名
        data_list: 要插入的数据列表，每个元素是一个字典
        
    Returns:
        bool: 插入成功返回True，失败返回False
        
    Example:
        data_list = [
            {
                'name': '张三',
                'email': 'zhangsan@example.com',
                'created_at': datetime.now(),
                'status': 1
            },
            {
                'name': '李四',
                'email': 'lisi@example.com',
                'created_at': datetime.now(),
                'status': 1
            }
        ]
        batch_insert_with_timestamp('users', data_list)
    """
    if not data_list:
        logger.warning("数据列表为空，跳过批量插入")
        return True
        
    connection = None
    try:
        connection = create_mysql_connection()
        with connection.cursor() as cursor:
            # 使用第一个数据项来确定列名
            columns = list(data_list[0].keys())
            placeholders = ', '.join(['%s'] * len(columns))
            columns_str = ', '.join(columns)
            
            sql = f"INSERT INTO {table_name} ({columns_str}) VALUES ({placeholders})"
            
            # 准备批量数据
            batch_values = []
            for data in data_list:
                values = []
                for key in columns:
                    value = data.get(key)
                    if hasattr(value, 'strftime'):  # 检查是否为datetime对象
                        values.append(value.strftime('%Y-%m-%d %H:%M:%S'))
                    else:
                        values.append(value)
                batch_values.append(values)
            
            # 执行批量插入
            cursor.executemany(sql, batch_values)
            connection.commit()
            
            logger.info(f"成功批量插入 {len(data_list)} 条数据到表 {table_name}")
            return True
            
    except Exception as e:
        logger.error(f"批量插入数据到表 {table_name} 失败: {e}")
        if connection:
            connection.rollback()
        return False
    finally:
        if connection:
            connection.close()

if __name__ == "__main__":
    print(test_mysql_connection())